﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Model;


namespace DAL
{
  public   class teacherinfoDAL
    {
        //列表展示
        public static DataTable getall(string tname)
        {
            string sql = "select * from teacher a left join connection b on a.tno=b.tno where 1=1";
            if (tname != "" && tname != null)
            {
                sql += string.Format("and a.tname like'%{0}%'", tname);
            }
            DataTable table = DBHelper.ExcuteTable(sql);
            return table;
        }

        //查询
        public static teacher getone(int tno)
        {
            string sql = "select * from teacher where tno=" + tno;
            DataTable table = DBHelper.ExcuteTable(sql);
            if (table.Rows.Count > 0)
            {
                teacher tea = new teacher();
                tea.tno = Convert.ToInt32(table.Rows[0]["tno"]);
                tea.password = table.Rows[0]["password"].ToString();
                tea.tname = table.Rows[0]["tname"].ToString();
                tea.Sex = table.Rows[0]["Sex"].ToString();
                return tea;
            }
            return null;
        }

        //删除
        public static int Delete(int tno)
        {
            string sql = string.Format("delete from teacher where tno='{0}'", tno);
            int num = DBHelper.ExecuteNonQuery(sql);
            return num;
        }

        //添加
        public static int Add(teacher Teacher)
        {
            string sql = string.Format("insert into teacher values({0},'{1}','{2}','{3}')", Teacher.tno, Teacher.password, Teacher.tname, Teacher.Sex);
            int num = DBHelper.ExecuteNonQuery(sql);
            return num;
        }

        //修改
        public static int update(string password, string name, string sex, int tno)
        {
            string sql = string.Format("update teacher set tname='{0}',password='{1}',sex='{2}' where tno={3}",
                name, password, sex, tno);
            return DBHelper.ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static teacher roomID(int tno)
        {
            string sql = "select * from teacher where tno=" + tno;
            DataTable dt = DBHelper.ExcuteTable(sql);
            if (dt.Rows.Count > 0)
            {
                teacher model = new teacher();
                model.tno = Convert.ToInt32(dt.Rows[0]["tno"]);
                model.tname = dt.Rows[0]["tname"].ToString();
                model.Sex = dt.Rows[0]["Sex"].ToString();
                model.password = dt.Rows[0]["password"].ToString();

                return model;
            }
            return null;
        }

    }
}
